package cn.hui.utils;

import cn.hui.model.Column;
import cn.hui.model.Schema;
import cn.hui.model.Table;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



/**
 * 基础信息收集（库、表、字段）
 * 
 * @author yang
 *
 */
public class BasicInfoCollect {
	

	public static List<Schema> getSchematas(Connection connection) throws SQLException {
		List<Schema> list = new ArrayList<Schema>();
		PreparedStatement ps = connection.prepareStatement("SELECT * FROM SCHEMATA");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Schema sc = new Schema();
			sc.setCatalogName(rs.getString("CATALOG_NAME"));
			sc.setSchemaName(rs.getString("SCHEMA_NAME"));
			sc.setDefaultCharacterSetName(rs.getString("DEFAULT_CHARACTER_SET_NAME"));
			sc.setDefaultCollationName(rs.getString("DEFAULT_COLLATION_NAME"));
			sc.setSqlPath(rs.getString("SQL_PATH"));
			list.add(sc);
		}
		rs.close();
		//connection.close();
		return list;
	}

	public static List<Table> getTables(Connection connection,String database) throws SQLException {
		List<Table> list = new ArrayList<Table>();
		PreparedStatement ps = connection.prepareStatement("SELECT * FROM TABLES WHERE TABLE_SCHEMA=?");
		ps.setString(1, database);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Table table = new Table();
			table.setTableCatalog(rs.getString("TABLE_CATALOG"));
			table.setTableSchema(rs.getString("TABLE_SCHEMA"));
			table.setTableName(rs.getString("TABLE_NAME"));
			table.setTableType(rs.getString("TABLE_TYPE"));
			table.setEngine(rs.getString("ENGINE"));
			table.setVersion(rs.getLong("VERSION"));
			table.setRowFormat(rs.getString("ROW_FORMAT"));
			table.setTableRows(rs.getLong("TABLE_ROWS"));
			table.setAvgRowLength(rs.getLong("AVG_ROW_LENGTH"));
			table.setDataLength(rs.getLong("DATA_LENGTH"));
			table.setMaxDataLength(rs.getLong("MAX_DATA_LENGTH"));
			table.setIndexLength(rs.getLong("INDEX_LENGTH"));
			table.setDataFree(rs.getLong("DATA_FREE"));
			table.setAutoIncrement(rs.getLong("AUTO_INCREMENT"));
			table.setCreateTime(rs.getTimestamp("CREATE_TIME"));
			table.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
			table.setCheckTime(rs.getTimestamp("CHECK_TIME"));
			table.setTableCollation(rs.getString("TABLE_COLLATION"));
			table.setCheckSum(rs.getLong("CHECKSUM"));
			table.setCreateOptions(rs.getString("CREATE_OPTIONS"));
			table.setTableComment(rs.getString("TABLE_COMMENT"));
			list.add(table);
		}
		rs.close();
		//con.close();
		return list;
	}

	public static List<Column> getColumns(Connection connection,String database, String table) throws SQLException {
		List<Column> list = new ArrayList<Column>();
		PreparedStatement ps = connection.prepareStatement("SELECT * FROM COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?");
		ps.setString(1, database);
		ps.setString(2, table);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Column col = new Column();
			col.setTableCatalog(rs.getString("TABLE_CATALOG"));
			col.setTableSchema(rs.getString("TABLE_SCHEMA"));
			col.setTableName(rs.getString("TABLE_NAME"));
			col.setColumnName(rs.getString("COLUMN_NAME"));
			col.setOrdinalPosition(rs.getLong("ORDINAL_POSITION"));
			col.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
			col.setIsNullable(rs.getString("IS_NULLABLE"));
			col.setDataType(rs.getString("DATA_TYPE"));
			col.setCharacterMaximumLength(rs.getLong("CHARACTER_MAXIMUM_LENGTH"));
			col.setCharacterOctetLength(rs.getLong("CHARACTER_OCTET_LENGTH"));
			col.setNumericPrecision(rs.getLong("NUMERIC_PRECISION"));
			col.setNumericScale(rs.getLong("NUMERIC_SCALE"));
			col.setCharacterSetName(rs.getString("CHARACTER_SET_NAME"));
			col.setCollationName(rs.getString("COLLATION_NAME"));
			col.setColumnType(rs.getString("COLUMN_TYPE"));
			col.setColumnKey(rs.getString("COLUMN_KEY"));
			col.setExtra(rs.getString("EXTRA"));
			col.setPrivileges(rs.getString("PRIVILEGES"));
			col.setColumnComment(rs.getString("COLUMN_COMMENT"));
			list.add(col);
		}
		return list;
	}

	public static void main(String[] args) throws SQLException {
//		String database = "jfinal_demo";
//		for(Table table:new BasicInfoCollect().getTables(database)){
//			System.out.println(table.getTableName());
//		}
	}
}
